/**
 * 
 */
package com.feonix.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * @CopyRight (c)1993-2017
 * @Project m-complat-common
 * @Comments EXCEL工具类
 * @NameSpace com.feonix.common.util
 * @Author Feonixzw
 * @CreateDate 2017年12月7日
 */
public class ExcelUtil {

	private static final Log logger = LogFactory.getLog(ExcelUtil.class);

	private WritableCellFormat wcf_num = null;

	private WritableCellFormat wcf_percent = null;

	public ExcelUtil() {
		init();
	}

	public ExcelUtil(HttpServletRequest httpServletRequest) {
		init();
	}

	public void init() {
		try {
			NumberFormat numberFormat = new NumberFormat("#");
			this.wcf_num = new WritableCellFormat(numberFormat);

			this.wcf_num.setAlignment(Alignment.CENTRE);
			this.wcf_num.setVerticalAlignment(VerticalAlignment.CENTRE);
			this.wcf_num.setBorder(Border.ALL, BorderLineStyle.THIN);
			this.wcf_num.setWrap(true);

			this.wcf_percent = new WritableCellFormat(NumberFormats.PERCENT_INTEGER);

			this.wcf_percent.setAlignment(Alignment.CENTRE);
			this.wcf_percent.setVerticalAlignment(VerticalAlignment.CENTRE);
			this.wcf_percent.setBorder(Border.ALL, BorderLineStyle.THIN);
			this.wcf_percent.setWrap(true);
		} catch (Exception localException) {
			logger.error("init Error:", localException);
			return;
		}
	}

	/**
	 * 读取Execl数据, 放在数组中返回
	 * 
	 * @param file
	 *            待处理的文件
	 * @return (Map中的key为表单中首行的头,value为每一行对应的值)
	 */
	public static List<Map<String, String>> readExcel(File file) {
		if (file == null) {
			return null;
		}
		return readExcel(file.getAbsolutePath());
	}

	/**
	 * 读取Execl数据, 放在LIST中返回
	 * 
	 * @param filePath
	 *            Excel表单的绝对路径
	 * @return (Map中的key为表单中首行的头,value为每一行对应的值)
	 */
	public static List<Map<String, String>> readExcel(String filePath) {
		List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
		Workbook workBook = null;
		InputStream fileInputStream = null;
		List<String> contentsList = new ArrayList<String>();
		Map<String, String> hashMap = null;
		String key = "";
		String value = "";
		try {
			fileInputStream = new FileInputStream(filePath);
			workBook = Workbook.getWorkbook(fileInputStream);
			Sheet sheet = workBook.getSheet(0);
			// 获取头head，即第一行
			for (int i = 0; i < sheet.getColumns(); i++) {
				contentsList.add(sheet.getCell(i, 0).getContents());
			}
			for (int i = 1; i < sheet.getRows(); i++) {
				hashMap = new HashMap<String, String>();
				for (int j = 0; j < sheet.getColumns(); j++) {
					key = contentsList.get(j);
					value = sheet.getCell(j, i).getContents();
					hashMap.put(key, value);
				}
				resultList.add(hashMap);
			}
		} catch (Exception localException) {
			logger.error("readExcel Error");
			resultList = null;
		} finally {
			if (workBook != null) {
				workBook.close();
			}
			if (fileInputStream != null)
				try {
					fileInputStream.close();
				} catch (IOException localIOException2) {
				}
		}
		return resultList;
	}

	/**
	 * 方法名：readExcel 参数：filePath为Execl文件的绝对路径 用途：读取Execl数据, 放在数组中返回
	 * 返回类型：ArrayList (al_head, al, al....)
	 * 
	 * @param filePath
	 * @return
	 */
	public static Map<String, List<String[]>> readAllExcel(String filePath) {
		Map<String, List<String[]>> resultHashMap = new HashMap<String, List<String[]>>();
		Workbook workbook = null;
		try {
			InputStream inputStream = new FileInputStream(filePath);
			workbook = Workbook.getWorkbook(inputStream);
			int sheetLength = workbook.getSheets().length;
			for (int i = 0; i < sheetLength; ++i) {
				// 从第一个sheet开始获取
				Sheet sheet = workbook.getSheet(i);
				List<String[]> resultList = new ArrayList<String[]>();
				String[] headArray = new String[sheet.getColumns()];
				// 获取sheet的头
				for (int j = 0; j < sheet.getColumns(); j++) {
					headArray[j] = sheet.getCell(j, 0).getContents();
				}
				resultList.add(headArray);
				// 循环遍历获取具体的内容
				for (int j = 1; j < sheet.getRows(); j++) {
					String[] contentArr = new String[sheet.getColumns()];
					for (int k = 0; k < sheet.getColumns(); k++) {
						contentArr[k] = sheet.getCell(k, j).getContents();
					}
					resultList.add(contentArr);
				}
				resultHashMap.put(sheet.getName(), resultList);
			}
		} catch (Exception localException) {
			logger.error("readExcel Error");
		} finally {
			if (workbook != null) {
				workbook.close();
			}
		}
		return resultHashMap;
	}

	/**
	 * 写Excel文件
	 * 
	 * @param filePath
	 * @param al
	 *            ArrayList (al_head, al_value, al_value....)
	 */
	public static void writeExcel(String filePath, List<List<String>> al) {
		File file = new File(filePath);
		WritableWorkbook writableWorkbook = null;
		try {
			writableWorkbook = Workbook.createWorkbook(file);

			WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 0);
			List<String> headContents = new ArrayList<String>();
			if (al.size() > 0) {
				headContents = al.get(0);
				al.remove(0);
			}
			String head = null;
			// 设置头
			for (int i = 0; i < headContents.size(); i++) {
				head = headContents.get(i);
				Label label = new Label(i, 0, head);
				writableSheet.addCell(label);
			}
			List<String> rowList = null;
			// 设置内容
			for (int i = 0; i < al.size(); i++) {
				rowList = al.get(i);
				for (int j = 0; j < rowList.size(); j++) {
					String columnStr = rowList.get(j);
					Label label = new Label(j, i + 1, columnStr);
					writableSheet.addCell(label);
				}

			}
			writableWorkbook.write();
		} catch (IOException iOException) {
			logger.error("writeExcel Error:", iOException);
		} catch (WriteException writeException) {
			logger.error("writeExcel Error:", writeException);
		} finally {
			try {
				if (writableWorkbook != null) {
					writableWorkbook.close();
				}
			} catch (Exception localException3) {
				logger.error("writeExcel Error:", localException3);
				return;
			}
		}

	}

	/**
	 * 写多个Excel文件
	 * 
	 * @param filePath
	 * @param sheets
	 * @param al
	 *            Map (al_head, al_value, al_value....)
	 */
	public static void writeAllExcel(String filePath, String[] sheets, Map<String, List<String[]>> al) {
		File file = new File(filePath);
		WritableWorkbook writableWorkbook = null;
		try {
			writableWorkbook = Workbook.createWorkbook(file);

			if (al.size() > 0) {
				for (int i = 0; i < sheets.length; ++i) {
					WritableSheet writableSheet = writableWorkbook.createSheet(sheets[i], i);
					String[] sheetHeadArr = al.get(sheets[i]).get(0);
					String[] contents;
					String sheetHead = "";
					// 写入头部信息
					for (int j = 0; j < sheetHeadArr.length; j++) {
						sheetHead = sheetHeadArr[j];
						Label label = new Label(j, 0, sheetHead);
						writableSheet.addCell(label);
					}
					for (int j = 1; j < al.get(sheets[i]).size(); j++) {
						contents = al.get(sheets[i]).get(j);
						for (int k = 0; k < contents.length; k++) {
							String value = contents[k];
							Label label = new Label(k, j, value);
							writableSheet.addCell(label);
						}
					}
				}
			}
			writableWorkbook.write();
		} catch (IOException iOException) {
			logger.error("writeExcel Error:", iOException);
		} catch (WriteException writeException) {
			logger.error("writeExcel Error:", writeException);
		} finally {
			try {
				if (writableWorkbook != null) {
					writableWorkbook.close();
				}
			} catch (Exception exception) {
				logger.error("writeExcel Error:", exception);
				return;
			}
		}
	}

	/**
	 * 添加cell 可以设置 cell 行高、列宽
	 * 
	 * sheet - WritableSheet
	 * 
	 * @param col
	 *            - 列数
	 * @param row
	 *            - 行数
	 * @param font
	 *            - 字体
	 * @param fontsize
	 *            - 大小
	 * @param bold
	 *            - 是否加粗
	 * @param rowheight
	 *            - 行高
	 * @param colwidth
	 *            - 列宽
	 * @param value
	 *            - 值
	 * 
	 */
	private void addCell(WritableSheet sheet, int col, int row, String font, int fontsize, boolean bold, int rowheight,
			int colwidth, String value) {
		addCell(sheet, col, row, font, fontsize, bold, "center", rowheight, colwidth, value);
	}

	/**
	 * 添加cell 可以设置 cell 行高、列宽 , 水平位置
	 * 
	 * @param sheet
	 *            - WritableSheet
	 * 
	 * @param col
	 *            - int
	 * @param row
	 *            - int
	 * @param font
	 *            - String
	 * @param fontsize
	 *            - int
	 * @param bold
	 *            - boolean
	 * @param align
	 *            - 水平位置
	 * @param rowheight
	 *            - int
	 * @param colwidth
	 *            - int
	 * @param value
	 *            - String
	 */
	private void addCell(WritableSheet sheet, int col, int row, String font, int fontsize, boolean bold, String align,
			int rowheight, int colwidth, String value) {
		try {
			WritableFont writableFont = new WritableFont(WritableFont.createFont(font), fontsize);
			if (bold) {
				writableFont = new WritableFont(WritableFont.createFont(font), fontsize, WritableFont.BOLD);
			}
			WritableCellFormat writableCellFormat = new WritableCellFormat(writableFont);
			if ("center".equals(align))
				writableCellFormat.setAlignment(Alignment.CENTRE);
			else if ("left".equals(align))
				writableCellFormat.setAlignment(Alignment.LEFT);
			else if ("right".equals(align)) {
				writableCellFormat.setAlignment(Alignment.RIGHT);
			}
			writableCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
			writableCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			writableCellFormat.setWrap(true);

			Label label = new Label(col, row, value, writableCellFormat);

			sheet.addCell(label);

			if (rowheight != 0) {
				sheet.setRowView(row, rowheight);
			}

			if (colwidth == 0)
				return;
			sheet.setColumnView(col, colwidth);
		} catch (Exception localException) {
			return;
		}
	}

	private void addCell(WritableSheet sheet, int col, int row, String font, int fontsize, boolean bold, String value)
			throws Exception {
		addCell(sheet, col, row, font, fontsize, bold, 0, 0, value);
	}

	private void addCellNumber(WritableSheet sheet, WritableCellFormat writableCellFormat, int col, int row,
			double value) throws Exception {
		Number localNumber = new Number(col, row, value, writableCellFormat);
		sheet.addCell(localNumber);
	}

	public static void main(String[] paramArrayOfString) {
		ExcelUtil localExcelUtil = new ExcelUtil();
		WritableWorkbook localWritableWorkbook = null;
		try {
			String str = "";
			File localFile = new File(str);
			if (!(localFile.exists())) {
				try {
					localFile.mkdir();
				} catch (Exception localException3) {
					logger.error("mkdirs file error", localException3);
				}
			}
			str = str + "按评议人身份统计满意度（行政和执法部门）.xls";

			localWritableWorkbook = Workbook.createWorkbook(new File(str));

			WritableSheet localWritableSheet = localWritableWorkbook.createSheet("sheet1", 0);

			localExcelUtil.addCell(localWritableSheet, 0, 0, "宋体 ", 12, true, "区级机关部门一把手信任投票统计");

			localWritableSheet.mergeCells(0, 0, 6, 0);

			localExcelUtil.addCell(localWritableSheet, 0, 1, "宋体 ", 10, false, "多少家）");

			localWritableSheet.mergeCells(0, 1, 6, 1);

			localExcelUtil.addCell(localWritableSheet, 0, 2, "宋体  ", 9, true, 0, 5, "序号");

			localWritableSheet.mergeCells(0, 2, 0, 4);

			localExcelUtil.addCell(localWritableSheet, 1, 2, "宋体", 9, true, 0, 15, "部门（单位）");

			localWritableSheet.mergeCells(1, 2, 1, 4);

			localExcelUtil.addCell(localWritableSheet, 2, 2, "宋体", 9, true, 0, 10, "主要负责人");

			localWritableSheet.mergeCells(2, 2, 2, 4);

			int i = 3;
			localExcelUtil.addCellNumber(localWritableSheet, localExcelUtil.wcf_num, 3, i, 10.0D);
			localExcelUtil.addCellNumber(localWritableSheet, localExcelUtil.wcf_num, 4, i, 11.0D);
			localExcelUtil.addCellNumber(localWritableSheet, localExcelUtil.wcf_num, 5, i, 12.0D);
			localExcelUtil.addCellNumber(localWritableSheet, localExcelUtil.wcf_num, 6, i, 13.0D);
			localExcelUtil.addCellNumber(localWritableSheet, localExcelUtil.wcf_percent, 7, i, 0.23D);

			localWritableWorkbook.write();
			localWritableWorkbook.close();
		} catch (Exception localException1) {
			try {
				if (localWritableWorkbook != null)
					localWritableWorkbook.close();
			} catch (Exception localException2) {
				logger.debug("[setMerpHome] error：", localException2);
			}
		}
	}

}
